-- create database
CREATE DATABASE ATMSystem
GO
-- create table
USE ATMSystem
GO
--
CREATE TABLE dbo.Customers(
	CustID int IDENTITY(1, 1) NOT NULL,
	Name nvarchar(100) NOT NULL,
	Phone varchar(50) NOT NULL,
	Email varchar(100) NOT NULL,
	CustAddress nvarchar(200) NOT NULL,
	CONSTRAINT PK_Customers PRIMARY KEY(CustID) )
GO
--
CREATE TABLE dbo.OverDrafts(
	ODID int IDENTITY(1, 1) NOT NULL,
	ODValue decimal NOT NULL,
	CONSTRAINT PK_OverDrafts PRIMARY KEY(ODID) )
GO
--
CREATE TABLE dbo.WithdrawLimits(
	WDID int IDENTITY(1, 1) NOT NULL,
	WDValue decimal NOT NULL,
	CONSTRAINT PK_WithdrawLimits PRIMARY KEY(WDID) )
GO
--
CREATE TABLE dbo.Accounts(
	AccountID int IDENTITY(1, 1) NOT NULL,
	AccountNo varchar(50) NOT NULL,
	Balance decimal NOT NULL,
	CustID int NOT NULL,
	ODID int NOT NULL,
	WDID int NOT NULL,
	CONSTRAINT PK_Accounts PRIMARY KEY(AccountID),
	CONSTRAINT FK_Account_Customer FOREIGN KEY(CustID) REFERENCES dbo.Customers(CustID),
	CONSTRAINT FK_Account_OverDraft FOREIGN KEY(ODID) REFERENCES dbo.OverDrafts(ODID),
	CONSTRAINT FK_Account_WithdrawLimit FOREIGN KEY(WDID) REFERENCES dbo.WithdrawLimits(WDID) )
GO
--
CREATE TABLE dbo.Cards(
	CardNo varchar(16) NOT NULL,
	Status varchar(30) NOT NULL,
	AccountID int NOT NULL,
	PIN varchar(6) NOT NULL,
	StartDate datetime NOT NULL,
	ExpiredDate datetime NOT NULL,
	Attempt int NOT NULL,
	CONSTRAINT PK_Cards PRIMARY KEY(CardNo),
	CONSTRAINT FK_Card_Account FOREIGN KEY(AccountID) REFERENCES dbo.Accounts(AccountID) )
GO
--
CREATE TABLE dbo.ATMs(
	ATMID int IDENTITY(1, 1) NOT NULL,
	Branch nvarchar(50) NOT NULL,
	ATMAddress nvarchar(100) NOT NULL,
	CONSTRAINT PK_ATMs PRIMARY KEY(ATMID) )
GO
--
CREATE TABLE dbo.Moneys(
	MoneyID int IDENTITY(1, 1) NOT NULL,
	MoneyValue decimal NOT NULL,
	CONSTRAINT PK_Moneys PRIMARY KEY(MoneyID) )
GO
--
CREATE TABLE dbo.LogTypes(
	LogTypeID int IDENTITY(1, 1) NOT NULL,
	Description nvarchar(100) NOT NULL,
	CONSTRAINT PK_LogTypes PRIMARY KEY(LogTypeID) )
GO
--
CREATE TABLE dbo.Stocks(
	StockID int IDENTITY(1, 1) NOT NULL,
	MoneyID int NOT NULL,
	ATMID int NOT NULL,
	Quantity int NOT NULL,
	CONSTRAINT PK_Stocks PRIMARY KEY(StockID),
	CONSTRAINT FK_Stock_Money FOREIGN KEY(MoneyID) REFERENCES dbo.Moneys(MoneyID),
	CONSTRAINT FK_Stock_ATM FOREIGN KEY(ATMID) REFERENCES dbo.ATMs(ATMID) )
GO
--
CREATE TABLE dbo.Logs(
	LogID int IDENTITY(1, 1) NOT NULL,
	LogTypeID int NOT NULL,
	ATMID int NOT NULL,
	CardNo varchar(16) NOT NULL,
	LogDate datetime NOT NULL,
	Amount decimal NOT NULL,
	Details varchar(100) NOT NULL,
	CONSTRAINT PK_Logs PRIMARY KEY(LogID),
	CONSTRAINT FK_Log_LogType FOREIGN KEY(LogTypeID) REFERENCES dbo.LogTypes(LogTypeID),
	CONSTRAINT FK_Log_ATM FOREIGN KEY(ATMID) REFERENCES dbo.ATMs(ATMID), 
	CONSTRAINT FK_Log_Card FOREIGN KEY(CardNo) REFERENCES dbo.Cards(CardNo) )
GO
--
CREATE TABLE dbo.Configs(
	ConfigID int IDENTITY(1, 1) NOT NULL,
	MinWithdraw decimal NOT NULL,
	MaxWithdraw decimal NOT NULL,
	DateModified datetime NOT NULL,
	CONSTRAINT PK_Configs PRIMARY KEY(ConfigID) )
GO

-- Create stored procedure

-- Table Accounts
CREATE PROCEDURE AccountsInsert
(
	@AccountNo varchar(50),
	@Balance decimal,
	@CustID int,
	@ODID int,
	@WDID int
)
AS
BEGIN
	INSERT INTO Accounts(AccountNo, Balance, CustID, ODID, WDID)
	VALUES(@AccountNo, @Balance, @CustID, @ODID, @WDID)
END

GO
CREATE PROCEDURE AccountsUpDate
(
	@AccountID int,
	@AccountNo varchar(50),
	@Balance decimal,
	@CustID int,
	@ODID int,
	@WDID int
)
AS
BEGIN
	UPDATE Accounts SET AccountNo= @AccountNo, Balance= @Balance, CustID= @CustID, ODID= @ODID, WDID= @WDID
	WHERE AccountID = @AccountID
END

GO
CREATE PROCEDURE AccountsDelete
(
	@AccountID int
)
AS
BEGIN
	DELETE FROM Accounts WHERE AccountID= @AccountID
END

GO
CREATE PROCEDURE AccountsSelectAll

AS
BEGIN
	SELECT * FROM Accounts
END

GO
CREATE PROCEDURE AccountsSelectByID
(
	@AccountID int
)
AS
BEGIN
	SELECT * FROM Accounts WHERE AccountID= @AccountID
END

GO
-- Table ATMs
CREATE PROCEDURE ATMsInsert
(
	@Branch nvarchar(50),
	@ATMAddress nvarchar(100)
)
AS
BEGIN
	INSERT INTO ATMs(Branch, ATMAddress)
	VALUES(@Branch, @ATMAddress)
END

GO
CREATE PROCEDURE ATMsUpDate
(
	@ATMID int,
	@Branch nvarchar(50),
	@ATMAddress nvarchar(100)
)
AS
BEGIN
	UPDATE ATMs SET Branch= @Branch, ATMAddress= @ATMAddress
	WHERE ATMID = @ATMID
END

GO
CREATE PROCEDURE ATMsDelete
(
	@ATMID int
)
AS
BEGIN
	DELETE FROM ATMs WHERE ATMID= @ATMID
END

GO
CREATE PROCEDURE ATMsSelectAll

AS
BEGIN
	SELECT * FROM ATMs
END

GO
CREATE PROCEDURE ATMsSelectByID
(
	@ATMID int
)
AS
BEGIN
	SELECT * FROM ATMs WHERE ATMID= @ATMID
END

GO
-- Table Cards
CREATE PROCEDURE CardsInsert
(
	@CardNo varchar(16),
	@Status varchar(30),
	@AccountID int,
	@PIN varchar(6),
	@StartDate datetime,
	@ExpiredDate datetime,
	@Attempt int
)
AS
BEGIN
	INSERT INTO Cards(CardNo, Status, AccountID, PIN, StartDate, ExpiredDate, Attempt)
	VALUES(@CardNo, @Status, @AccountID, @PIN, @StartDate, @ExpiredDate, @Attempt)
END

GO
CREATE PROCEDURE CardsUpDate
(
	@CardNo varchar(16),
	@Status varchar(30),
	@AccountID int,
	@PIN varchar(6),
	@StartDate datetime,
	@ExpiredDate datetime,
	@Attempt int
)
AS
BEGIN
	UPDATE Cards SET Status= @Status, AccountID= @AccountID, PIN= @PIN, StartDate= @StartDate, ExpiredDate= @ExpiredDate, Attempt= @Attempt
	WHERE CardNo = @CardNo
END

GO
CREATE PROCEDURE CardsDelete
(
	@CardNo varchar(16)
)
AS
BEGIN
	DELETE FROM Cards WHERE CardNo= @CardNo
END

GO
CREATE PROCEDURE CardsSelectAll

AS
BEGIN
	SELECT * FROM Cards
END

GO
CREATE PROCEDURE CardsSelectByID
(
	@CardNo varchar(16)
)
AS
BEGIN
	SELECT * FROM Cards WHERE CardNo= @CardNo
END

GO
-- Table Configs
CREATE PROCEDURE ConfigsInsert
(
	@MinWithdraw decimal,
	@MaxWithdraw decimal,
	@DateModified datetime
)
AS
BEGIN
	INSERT INTO Configs(MinWithdraw, MaxWithdraw, DateModified)
	VALUES(@MinWithdraw, @MaxWithdraw, @DateModified)
END

GO
CREATE PROCEDURE ConfigsUpDate
(
	@ConfigID int,
	@MinWithdraw decimal,
	@MaxWithdraw decimal,
	@DateModified datetime
)
AS
BEGIN
	UPDATE Configs SET MinWithdraw= @MinWithdraw, MaxWithdraw= @MaxWithdraw, DateModified= @DateModified
	WHERE ConfigID = @ConfigID
END

GO
CREATE PROCEDURE ConfigsDelete
(
	@ConfigID int
)
AS
BEGIN
	DELETE FROM Configs WHERE ConfigID= @ConfigID
END

GO
CREATE PROCEDURE ConfigsSelectAll

AS
BEGIN
	SELECT * FROM Configs
END

GO
CREATE PROCEDURE ConfigsSelectByID
(
	@ConfigID int
)
AS
BEGIN
	SELECT * FROM Configs WHERE ConfigID= @ConfigID
END

GO
-- Table Customers
CREATE PROCEDURE CustomersInsert
(
	@Name nvarchar(100),
	@Phone varchar(50),
	@Email varchar(100),
	@CustAddress nvarchar(200)
)
AS
BEGIN
	INSERT INTO Customers(Name, Phone, Email, CustAddress)
	VALUES(@Name, @Phone, @Email, @CustAddress)
END

GO
CREATE PROCEDURE CustomersUpDate
(
	@CustID int,
	@Name nvarchar(100),
	@Phone varchar(50),
	@Email varchar(100),
	@CustAddress nvarchar(200)
)
AS
BEGIN
	UPDATE Customers SET Name= @Name, Phone= @Phone, Email= @Email, CustAddress= @CustAddress
	WHERE CustID = @CustID
END

GO
CREATE PROCEDURE CustomersDelete
(
	@CustID int
)
AS
BEGIN
	DELETE FROM Customers WHERE CustID= @CustID
END

GO
CREATE PROCEDURE CustomersSelectAll

AS
BEGIN
	SELECT * FROM Customers
END

GO
CREATE PROCEDURE CustomersSelectByID
(
	@CustID int
)
AS
BEGIN
	SELECT * FROM Customers WHERE CustID= @CustID
END

GO
-- Table Logs
CREATE PROCEDURE LogsInsert
(
	@LogTypeID int,
	@ATMID int,
	@CardNo varchar(16),
	@LogDate datetime,
	@Amount decimal,
	@Details varchar(100)
)
AS
BEGIN
	INSERT INTO Logs(LogTypeID, ATMID, CardNo, LogDate, Amount, Details)
	VALUES(@LogTypeID, @ATMID, @CardNo, @LogDate, @Amount, @Details)
END

GO
CREATE PROCEDURE LogsUpDate
(
	@LogID int,
	@LogTypeID int,
	@ATMID int,
	@CardNo varchar(16),
	@LogDate datetime,
	@Amount decimal,
	@Details varchar(100)
)
AS
BEGIN
	UPDATE Logs SET LogTypeID= @LogTypeID, ATMID= @ATMID, CardNo= @CardNo, LogDate= @LogDate, Amount= @Amount, Details= @Details
	WHERE LogID = @LogID
END

GO
CREATE PROCEDURE LogsDelete
(
	@LogID int
)
AS
BEGIN
	DELETE FROM Logs WHERE LogID= @LogID
END

GO
CREATE PROCEDURE LogsSelectAll

AS
BEGIN
	SELECT * FROM Logs
END

GO
CREATE PROCEDURE LogsSelectByID
(
	@LogID int
)
AS
BEGIN
	SELECT * FROM Logs WHERE LogID= @LogID
END

GO
-- Table LogTypes
CREATE PROCEDURE LogTypesInsert
(
	@Description nvarchar(100)
)
AS
BEGIN
	INSERT INTO LogTypes(Description)
	VALUES(@Description)
END

GO
CREATE PROCEDURE LogTypesUpDate
(
	@LogTypeID int,
	@Description nvarchar(100)
)
AS
BEGIN
	UPDATE LogTypes SET Description= @Description
	WHERE LogTypeID = @LogTypeID
END

GO
CREATE PROCEDURE LogTypesDelete
(
	@LogTypeID int
)
AS
BEGIN
	DELETE FROM LogTypes WHERE LogTypeID= @LogTypeID
END

GO
CREATE PROCEDURE LogTypesSelectAll

AS
BEGIN
	SELECT * FROM LogTypes
END

GO
CREATE PROCEDURE LogTypesSelectByID
(
	@LogTypeID int
)
AS
BEGIN
	SELECT * FROM LogTypes WHERE LogTypeID= @LogTypeID
END

GO
-- Table Moneys
CREATE PROCEDURE MoneysInsert
(
	@MoneyValue decimal
)
AS
BEGIN
	INSERT INTO Moneys(MoneyValue)
	VALUES(@MoneyValue)
END

GO
CREATE PROCEDURE MoneysUpDate
(
	@MoneyID int,
	@MoneyValue decimal
)
AS
BEGIN
	UPDATE Moneys SET MoneyValue= @MoneyValue
	WHERE MoneyID = @MoneyID
END

GO
CREATE PROCEDURE MoneysDelete
(
	@MoneyID int
)
AS
BEGIN
	DELETE FROM Moneys WHERE MoneyID= @MoneyID
END

GO
CREATE PROCEDURE MoneysSelectAll

AS
BEGIN
	SELECT * FROM Moneys
END

GO
CREATE PROCEDURE MoneysSelectByID
(
	@MoneyID int
)
AS
BEGIN
	SELECT * FROM Moneys WHERE MoneyID= @MoneyID
END

GO
-- Table OverDrafts
CREATE PROCEDURE OverDraftsInsert
(
	@ODValue decimal
)
AS
BEGIN
	INSERT INTO OverDrafts(ODValue)
	VALUES(@ODValue)
END

GO
CREATE PROCEDURE OverDraftsUpDate
(
	@ODID int,
	@ODValue decimal
)
AS
BEGIN
	UPDATE OverDrafts SET ODValue= @ODValue
	WHERE ODID = @ODID
END

GO
CREATE PROCEDURE OverDraftsDelete
(
	@ODID int
)
AS
BEGIN
	DELETE FROM OverDrafts WHERE ODID= @ODID
END

GO
CREATE PROCEDURE OverDraftsSelectAll

AS
BEGIN
	SELECT * FROM OverDrafts
END

GO
CREATE PROCEDURE OverDraftsSelectByID
(
	@ODID int
)
AS
BEGIN
	SELECT * FROM OverDrafts WHERE ODID= @ODID
END

GO
-- Table Stocks
CREATE PROCEDURE StocksInsert
(
	@MoneyID int,
	@ATMID int,
	@Quantity int
)
AS
BEGIN
	INSERT INTO Stocks(MoneyID, ATMID, Quantity)
	VALUES(@MoneyID, @ATMID, @Quantity)
END

GO
CREATE PROCEDURE StocksUpDate
(
	@StockID int,
	@MoneyID int,
	@ATMID int,
	@Quantity int
)
AS
BEGIN
	UPDATE Stocks SET MoneyID= @MoneyID, ATMID= @ATMID, Quantity= @Quantity
	WHERE StockID = @StockID
END

GO
CREATE PROCEDURE StocksDelete
(
	@StockID int

)
AS
BEGIN
	DELETE FROM Stocks WHERE StockID= @StockID
END

GO
CREATE PROCEDURE StocksSelectAll

AS
BEGIN
	SELECT * FROM Stocks
END

GO
CREATE PROCEDURE StocksSelectByID
(
	@StockID int
)
AS
BEGIN
	SELECT * FROM Stocks WHERE StockID= @StockID
END

GO
-- WithdrawLimits
CREATE PROCEDURE WithdrawLimitsInsert
(
	@WDValue decimal
)
AS
BEGIN
	INSERT INTO WithdrawLimits(WDValue)
	VALUES(@WDValue)
END

GO
CREATE PROCEDURE WithdrawLimitsUpDate
(
	@WDID int,
	@WDValue decimal
)
AS
BEGIN
	UPDATE WithdrawLimits SET WDValue= @WDValue
	WHERE WDID = @WDID
END

GO
CREATE PROCEDURE WithdrawLimitsDelete
(
	@WDID int
)
AS
BEGIN
	DELETE FROM WithdrawLimits WHERE WDID= @WDID
END

GO
CREATE PROCEDURE WithdrawLimitsSelectAll

AS
BEGIN
	SELECT * FROM WithdrawLimits
END

GO
CREATE PROCEDURE WithdrawLimitsSelectByID
(
	@WDID int
)
AS
BEGIN
	SELECT * FROM WithdrawLimits WHERE WDID= @WDID
END

GO
-- Insert record to database
dbo.CustomersInsert 'Tran Sy Khoa', '01673546356', 'khoats@gmail.com', 'Ha Nam'
GO
dbo.CustomersInsert 'Le Van Hieu', '01673543456', 'hieulv@gmail.com', 'Phu Tho'
GO
dbo.CustomersInsert 'Le Thi Ngoc', '01673453467', 'ngoclt@gmail.com', 'Thanh Hoa'
GO
dbo.OverDraftsInsert 2000000
GO
dbo.OverDraftsInsert 2500000
GO
dbo.OverDraftsInsert 3000000
GO
dbo.WithdrawLimitsInsert 2000000
GO
dbo.WithdrawLimitsInsert 2500000
GO
dbo.WithdrawLimitsInsert 3000000
GO
dbo.AccountsInsert '3425345964674', 3500000, 1, 3, 1
GO
dbo.AccountsInsert '1234567890123', 3500000, 2, 1, 3
GO
dbo.AccountsInsert '11112222333344', 3500000, 3, 2, 2
GO
dbo.CardsInsert '1234567890123456', 'normal', 3, '123456', '02-10-2010', '02-10-2014', 0
GO
dbo.CardsInsert '1111111122222222', 'normal', 1, '111111', '08-11-2011', '08-11-2014', 0
GO
dbo.CardsInsert '1111111111111111', 'normal', 2, '111111', '12-05-2012', '12-05-2015', 0
GO
dbo.MoneysInsert 1000000
GO
dbo.MoneysInsert 10000000
GO
dbo.MoneysInsert 100000000
GO
dbo.ATMsInsert 'Agribank', 'Nhon'
GO
dbo.ATMsInsert 'ACB', 'Cau Giay'
GO
dbo.ATMsInsert 'DongABank', 'My Dinh'
GO
dbo.StocksInsert 1, 2, 5
GO
dbo.StocksInsert 2, 1, 8
GO
dbo.StocksInsert 3, 3, 2
GO
dbo.LogTypesInsert 'Withdraw'
GO
dbo.LogTypesInsert 'Check Balance'
GO
dbo.LogTypesInsert 'Tranfer'
GO
dbo.LogsInsert 1, 1, '1111111122222222', '02-10-2013', 200000, 'success'
GO
dbo.LogsInsert 2, 3, '1234567890123456', '02-11-2013', 3000000, 'success'
GO
dbo.LogsInsert 1, 3, '1111111122222222', '05-10-2013', 2000000, 'success'
GO
dbo.LogsInsert 3, 2, '1234567890123456', '09-01-2014', 500000, 'To Le Thi Ngoc'
GO
dbo.LogsInsert 3, 2, '1111111122222222', '05-10-2014', 1000000, 'To Nguyen Thi Men'
GO
dbo.LogsInsert 1, 3, '1111111122222222', '08-10-2014', 2000000, 'success'
GO
dbo.LogsInsert 2, 3, '1111111122222222', '07-10-2014', 600000, 'success'
GO
dbo.LogsInsert 1, 3, '1111111122222222', '05-10-2014', 800000, 'success'
use ATMSystem
GO
dbo.LogsInsert 1, 3, '1111111122222222', '09-01-2014', 800000, 'success'
GO
dbo.ConfigsInsert '50000', '2000000', '01-12-2012'
GO
dbo.ConfigsInsert '100000', '5000000', '10-09-2012'
GO
dbo.ConfigsInsert '50000', '3000000', '10-02-2013'
GO

---pro Transfers
CREATE PROC dbo.Transfers
@Amount decimal,
@AccountID int,
@AccountID1 int,
@CardNo nvarchar(16),
@LogDate datetime,
@Details nvarchar(50)
AS
BEGIN TRAN
		UPDATE dbo.Accounts
		SET
		Balance=Balance - @Amount
		WHERE AccountID = @AccountID
		
		UPDATE dbo.Accounts
		SET
		Balance=Balance + @Amount
		WHERE AccountID = @AccountID1
		
		INSERT INTO dbo.Logs(LogTypeID,CardNo,LogDate,Amount,Details)
		VALUES('3',@CardNo,@LogDate,@Amount,@Details)
COMMIT
GO

--KhoaTS add proc
--proc Withdraw
CREATE PROC dbo.Withdraw
@Amount decimal,
@AccountIDWithdraw int,
@ATMID int,
@CardNo nvarchar(16),
@LogDate datetime,
@Details nvarchar(50)
AS
BEGIN TRAN
		UPDATE dbo.Accounts
		SET
		Balance=Balance- @Amount
		WHERE AccountID= @AccountIDWithdraw
		
		UPDATE dbo.Moneys
		SET
		MoneyValue = MoneyValue - @Amount
		where MoneyID = 1
		
		INSERT INTO dbo.Logs(LogTypeID,ATMID,CardNo,LogDate,Amount,Details)
		VALUES('1','1',@CardNo,@LogDate,@Amount,@Details)
COMMIT
GO

----proc AccountsSelectByBalance
CREATE PROCEDURE AccountsSelectByBalance
(
	@AccountID int
)
AS
BEGIN
	SELECT Balance FROM dbo.Accounts WHERE AccountID= @AccountID
END
GO


--proc MoneysSelectByMoneyValue
CREATE PROCEDURE MoneysSelectByMoneyValue
(
	@MoneyID int
)
AS
BEGIN
	SELECT MoneyValue FROM Moneys WHERE MoneyID= @MoneyID
END